package com.tcfvic.server;

import java.io.File;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Map.Entry;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.AliasToEntityMapResultTransformer;

import com.tcfvic.server.persistance.HibernateUtil;
import com.tcfvic.server.tools.ExcelCellGenerator;
import com.tcfvic.server.tools.ExcelCellGeneratorFactory;

public abstract class AbstractReportService extends AbstractService {
	private static final long serialVersionUID = -9072313638237343983L;
	private static final Logger logger = Logger
			.getLogger(AbstractReportService.class);
	protected static String SYSTEM_DIR;
	private ExcelCellGenerator cellFactory = ExcelCellGeneratorFactory
			.createDateCellGenerator();
	// protected static final String SYSTEM_DIR = "war/tcfvic";

	protected static final String DIRECTORY_NAME = "reports";

	public AbstractReportService() {
		try {
			SYSTEM_DIR = "/";
		} catch (Exception e) {

		}
	}

	protected void initialise() {
		System.out.println("Initialising Report Service");
		try {
			SYSTEM_DIR = getServletContext().getRealPath("/") + "tcfvic/";
		} catch (Exception e) {
			SYSTEM_DIR = "tcfvic/";
		}
		File file = new File(SYSTEM_DIR + "/" + DIRECTORY_NAME);
		file.mkdirs();
		System.out.println("Report directory created: "
				+ file.getAbsolutePath());
	}

	@SuppressWarnings("unchecked")
	public List<String> getPersonColumnNames(String prefix, String sql) {
		List<String> names = new ArrayList<String>();
		Session session = HibernateUtil.getSession();
		session.beginTransaction();
		Query query = session.createSQLQuery(sql);
		query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
		List<Map<String, Object>> aliasToValueMapList = query.list();

		if (aliasToValueMapList.size() > 0) {
			Map<String, Object> source = (Map<String, Object>) aliasToValueMapList
					.get(0);
			if (source.size() > 0) {
				for (Entry<String, Object> entry : source.entrySet()) {
					names.add(prefix + (String) entry.getKey());
				}
			}
		}
		return names;
	}

	public String generateExcelFile(String sql, String filename) {
		return generateExcelFile(new String[] { sql }, filename, null);
	}

	public String generateExcelFile(String[] sql, String filename,
			List<List<String>> columns) {
		List<String> sqlQueries = new ArrayList<String>();
		for (String s : sql) {
			sqlQueries.add(s);
		}
		ReportRequest reportRequest = new ReportRequest("Sheet 1", sqlQueries,
				columns);
		List<ReportRequest> requests = new ArrayList<ReportRequest>();
		requests.add(reportRequest);
		return generateExcelFile(requests, filename);
	}

	@SuppressWarnings("unchecked")
	public String generateExcelFile(List<ReportRequest> reports, String filename) {
		initialise();

		String fullFilePath = getDirectory() + "/" + filename;
		try {
			Session session;
			File file = new File(fullFilePath);
			WorkbookSettings ws = new WorkbookSettings();
			ws.setLocale(new Locale("en", "EN"));
			WritableWorkbook workbook = Workbook.createWorkbook(file, ws);

			for (ReportRequest request : reports) {
				int row = 0;
				int columnForQueriesCount = 0;
				WritableSheet s = workbook.createSheet(request.getSheetName(),
						0);
				for (String sql : request.getSqlQueries()) {
					boolean showTitleRow = true;
					session = HibernateUtil.getSession();
					session.beginTransaction();
					Query query = session.createSQLQuery(sql);
					query
							.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
					List<Map<String, Object>> aliasToValueMapList = query
							.list();

					// print the values to excel
					for (Map map : aliasToValueMapList) {
						if (map != null && map.size() > 0) {
							row++;
							int j = 0;
							if (showTitleRow) {
								showTitleRow = false;
								if (request.getColumns() != null) {
									for (String column : request.getColumns()
											.get(columnForQueriesCount)) {
										s.addCell(new Label(j, row - 1,
												column != null ? column : ""));
										j++;
									}
								} else {
									for (Object column : map.keySet()) {
										s.addCell(new Label(j, row - 1, column
												.toString() != null ? column
												.toString() : ""));
										j++;
									}
								}
								j = 0;
							}
							if (request.getColumns() != null) {
								for (String column : request.getColumns().get(
										columnForQueriesCount)) {
									if (map.get(column) == null
											|| map.get(column) instanceof String) {
										Label label = new Label(j, row, map
												.get(column) != null ? map.get(
												column).toString() : "");
										s.addCell(label);
									} else if (map.get(column) == null
											|| map.get(column) instanceof Date) {
										try {
											s.addCell(cellFactory
													.getCell(j, row, (Date) map
															.get(column)));
										} catch (Exception e) {
										}
									} else {
										try {
											Formula f = new Formula(j, row, map
													.get(column) != null ? map
													.get(column).toString()
													: "0");
											s.addCell(f);
										} catch (Exception e) {
										}
									}
									j++;
								}
							} else {
								for (Object colEntrySet : map.entrySet()) {
									if (colEntrySet instanceof Map.Entry) {

										Map.Entry column = (Map.Entry) colEntrySet;
										if (column != null
												&& column.getValue() instanceof String) {
											Label label = new Label(j, row,
													column.getValue()
															.toString());
											s.addCell(label);
										} else if (column != null
												&& column.getValue() instanceof Date) {
											try {
												s.addCell(cellFactory.getCell(
														j, row, (Date) column
																.getValue()));
											} catch (Exception e) {
												e.printStackTrace();
											}
										} else if (column != null) {
											try {
												Formula f = new Formula(j, row,
														column != null ? column
																.getValue()
																.toString()
																: "0");
												s.addCell(f);
											} catch (Exception e) {
											}
										}
										j++;
									}
								}
							}

						}
					}
					session.getTransaction().commit();
					columnForQueriesCount++;
					row += 2;
				}
			}

			logger.info("File location: " + file.getAbsoluteFile());

			workbook.write();
			workbook.close();
		} catch (Exception e) {
			logger.error("Error captured in ReportService.getAllPersonDetails",
					e);
		}
		return DIRECTORY_NAME + "/" + filename;
	}

	@SuppressWarnings("unchecked")
	public String generateExcelFileOld(String sql, String filename) {
		initialise();
		String fullFilePath = getDirectory() + "/" + filename;
		try {

			Session session = HibernateUtil.getSession();
			session.beginTransaction();
			Query query = session.createSQLQuery(sql);
			query
					.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
			List<Map<String, Object>> aliasToValueMapList = query.list();

			List it = (session.createSQLQuery(sql).list());
			File file = new File(fullFilePath);
			logger.info("File location: " + file.getAbsoluteFile());
			WorkbookSettings ws = new WorkbookSettings();
			ws.setLocale(new Locale("en", "EN"));
			WritableWorkbook workbook = Workbook.createWorkbook(file, ws);
			WritableSheet s = workbook.createSheet("Sheet1", 0);
			// print the values to excel
			int row = 0;
			for (int i = 0; i < aliasToValueMapList.size(); i++) {
				if (it.get(i) != null) {
					row++;
					Map<String, Object> source = (Map<String, Object>) aliasToValueMapList
							.get(i);
					if (source.size() > 0) {
						int j = 0;
						for (Entry entry : source.entrySet()) {
							// if this is the first row, print the headings
							if (row == 1) {
								s.addCell(new Label(j, row - 1,
										entry.getKey() != null ? entry.getKey()
												.toString() : ""));
							}
							s.addCell(new Label(j, row,
									entry.getValue() != null ? entry.getValue()
											.toString() : ""));
							j++;
						}
					}
				}
			}

			workbook.write();
			workbook.close();
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error("Error captured in ReportService.getAllPersonDetails",
					e);
		}
		return DIRECTORY_NAME + "/" + filename;
	}

	@SuppressWarnings("unchecked")
	protected String generateAllDataHorizontal(String filename) {
		initialise();

		String fullFilePath = getDirectory() + "/" + filename;
		try {
			Session session;
			File file = new File(fullFilePath);
			WorkbookSettings ws = new WorkbookSettings();
			ws.setLocale(new Locale("en", "EN"));
			WritableWorkbook workbook = Workbook.createWorkbook(file, ws);

			int rowCounter = 0;
			int columnForQueriesCount = 0;
			WritableSheet workSheet = workbook.createSheet("All Data", 0);
			session = HibernateUtil.getSession();
			session.beginTransaction();
			String personSQLSelect = getPersonList().toString()
					.replace("[", "").replace("]", "");
			Query query = session
					.createSQLQuery("SELECT "
							+ personSQLSelect
							+ " "
							+ "FROM person p left outer join employmentdetails ed on ed.person_id = p.id "
							+ "left outer join workercompany wc on wc.id = ed.workercompany_id");
			query
					.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
			List<Map<String, Object>> personResultSet = query.list();

			writeColHeaders(workSheet, getPersonList(), getAddressList(),
					getCourseList(), getCourseList(), getCourseList(),
					getCourseList(), getCourseList(), getCourseList(),
					getCourseList(), getCourseList(), getCourseList(),
					getCourseList(), getExpenseList(), getExpenseList(),
					getExpenseList(), getExpenseList(), getExpenseList(),
					getExpenseList(), getExpenseList());
			// print the values to excel
			rowCounter++;
			for (Map person : personResultSet) {
				if (person != null && person.size() > 0) {
					rowCounter++;
					Integer personID = (Integer) person.get("id");
					// get data
					List<Map<String, Object>> personAddress = getAddressMap(
							session, personID);
					List<Map<String, Object>> personCourses = getCourseMap(
							session, personID);
					List<Map<String, Object>> personExpenses = getExpenseMap(
							session, personID);

					// print details
					Integer colCounter = 0;
					writeCells(rowCounter, workSheet, person, colCounter,
							getPersonList());
					colCounter = getPersonList().size();
					writeCells(rowCounter, workSheet, personAddress.get(0),
							colCounter, getAddressList());
					int i = 0;
					for (Map personCourse : personCourses) {
						colCounter = getPersonList().size()
								+ getAddressList().size()
								+ (getCourseList().size() * i);
						writeCells(rowCounter, workSheet, personCourse,
								colCounter, getCourseList());
						i++;
					}
					i = 0;
					for (Map personExpense : personExpenses) {
						colCounter = getPersonList().size()
								+ getAddressList().size()
								+ (getCourseList().size() * 10)
								+ (getExpenseList().size() * i);
						writeCells(rowCounter, workSheet, personExpense,
								colCounter, getExpenseList());
						i++;
					}
					columnForQueriesCount++;
				}
			}

			logger.info("File location: " + file.getAbsoluteFile());

			session.getTransaction().commit();
			workbook.write();
			workbook.close();
		} catch (Exception e) {
			logger.error("Error captured in ReportService.generateAllData", e);
		}
		return DIRECTORY_NAME + "/" + filename;
	}

	@SuppressWarnings("unchecked")
	protected String generateAllDataVertical(String filename) {
		initialise();

		String fullFilePath = getDirectory() + "/" + filename;
		try {
			Session session;
			File file = new File(fullFilePath);
			WorkbookSettings ws = new WorkbookSettings();
			ws.setLocale(new Locale("en", "EN"));
			WritableWorkbook workbook = Workbook.createWorkbook(file, ws);

			int rowCounter = 0;
			int columnForQueriesCount = 0;
			WritableSheet workSheet = workbook.createSheet("All Data", 0);
			session = HibernateUtil.getSession();
			session.beginTransaction();
			String personSQLSelect = getPersonList().toString()
					.replace("[", "").replace("]", "");
			Query query = session
					.createSQLQuery("SELECT "
							+ personSQLSelect
							+ " "
							+ "FROM person p left outer join employmentdetails ed on ed.person_id = p.id "
							+ "left outer join workercompany wc on wc.id = ed.workercompany_id");
			query
					.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
			List<Map<String, Object>> personResultSet = query.list();

			writeColHeaders(workSheet, getPersonList(), getAddressList(),
					getCourseList(), getCourseList(), getCourseList(),
					getCourseList(), getCourseList(), getCourseList(),
					getCourseList(), getCourseList(), getCourseList(),
					getCourseList(), getExpenseList(), getExpenseList(),
					getExpenseList(), getExpenseList(), getExpenseList(),
					getExpenseList(), getExpenseList());
			// print the values to excel
			rowCounter++;
			for (Map person : personResultSet) {
				if (person != null && person.size() > 0) {
					rowCounter++;
					Integer personID = (Integer) person.get("id");
					// get data
					List<Map<String, Object>> personAddress = getAddressMap(
							session, personID);
					List<Map<String, Object>> personCourses = getCourseMap(
							session, personID);
					List<Map<String, Object>> personExpenses = getExpenseMap(
							session, personID);

					// print details
					Integer colCounter = 0;
					writeCells(rowCounter, workSheet, person, colCounter,
							getPersonList());
					colCounter = getPersonList().size();
					writeCells(rowCounter, workSheet, personAddress.get(0),
							colCounter, getAddressList());
					int i = 0;
					for (Map personCourse : personCourses) {
						colCounter = getPersonList().size()
								+ getAddressList().size()
								+ (getCourseList().size() * i);
						writeCells(rowCounter, workSheet, personCourse,
								colCounter, getCourseList());
						i++;
					}
					i = 0;
					for (Map personExpense : personExpenses) {
						colCounter = getPersonList().size()
								+ getAddressList().size()
								+ (getCourseList().size() * 10)
								+ (getExpenseList().size() * i);
						writeCells(rowCounter, workSheet, personExpense,
								colCounter, getExpenseList());
						i++;
					}
					columnForQueriesCount++;
				}
			}

			logger.info("File location: " + file.getAbsoluteFile());

			session.getTransaction().commit();
			workbook.write();
			workbook.close();
		} catch (Exception e) {
			logger.error("Error captured in ReportService.generateAllData", e);
		}
		return DIRECTORY_NAME + "/" + filename;
	}

	@SuppressWarnings("unchecked")
	private List<Map<String, Object>> getAddressMap(Session session,
			Integer personID) {
		String addressSQLSelect = getAddressList().toString().replace("[", "")
				.replace("]", "");
		String addressSQL = "SELECT " + addressSQLSelect + " "
				+ "FROM person p "
				+ "join personaddress pa on pa.person_id = p.id "
				+ "join address a on pa.address_id = a.id " + "where p.id = "
				+ personID + " order by dateofarrival desc ";
		Query query = session.createSQLQuery(addressSQL);
		query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
		return query.list();
	}

	@SuppressWarnings("unchecked")
	private List<Map<String, Object>> getCourseMap(Session session,
			Integer personID) {
		String courseSQLSelect = getCourseList().toString().replace("[", "")
				.replace("]", "");
		String courseSQL = "SELECT "
				+ courseSQLSelect
				+ " "
				+ "FROM person p "
				+ "left join personcourse pc on p.id = pc.person_id "
				+ "join course c on pc.course_id = c.id "
				+ "left outer join rto r on r.id = pc.rto_id "
				+ "left outer join expense e on (p.id = e.person_id and c.id = e.relatedCourse and e.description like 'Course fees') "
				+ "left outer join fundingsource fs on fs.id = e.fundingsource_id "
				+ "where p.id = " + personID + " ";
		Query query = session.createSQLQuery(courseSQL);
		query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
		return query.list();
	}

	@SuppressWarnings("unchecked")
	private List<Map<String, Object>> getExpenseMap(Session session,
			Integer personID) {
		String expenseSQLSelect = getExpenseList().toString().replace("[", "")
				.replace("]", "");
		String expenseSQL = "SELECT "
				+ expenseSQLSelect
				+ " "
				+ "FROM person p "
				+ "left outer join expense e on e.person_id = p.id "
				+ "left outer join fundingsource fs on e.fundingsource_id = fs.id "
				+ "where p.id = "
				+ personID
				+ " and (e.description not like 'Course fees' or e.description is null) ";

		Query query = session.createSQLQuery(expenseSQL);
		query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
		return query.list();
	}

	@SuppressWarnings("unchecked")
	private void writeCells(int row, WritableSheet s, Map map, int j,
			List<String> colList) throws WriteException, RowsExceededException {
		for (String colName : colList) {
			Object column = map.get(colName
					.substring(colName.indexOf(" as ") + 4));
			if (column != null && column instanceof String) {
				Label label = new Label(j, row, column.toString());
				s.addCell(label);
			} else if (column != null && column instanceof Date) {
				try {
					// Datre f = new Formula(j, row, column != null ? column
					// .toString() : "0");
					s.addCell(cellFactory.getCell(j, row, (Date) column));
				} catch (Exception e) {
				}
			} else if (column != null) {
				try {
					Formula f = new Formula(j, row, column != null ? column
							.toString() : "0");
					s.addCell(f);
				} catch (Exception e) {
				}
			}
			j++;
		}
	}

	private void writeColHeaders(WritableSheet s, List<String>... colListArray)
			throws WriteException, RowsExceededException {
		int i = 0;
		for (List<String> colList : colListArray) {
			for (String colName : colList) {
				Label label = new Label(i, 0, colName.substring(colName
						.indexOf(" as ") + 4));
				s.addCell(label);
				i++;
			}
		}
	}

	private List<String> getPersonList() {
		List<String> personList = new ArrayList<String>();
		personList.add("p.id as id");
		personList.add("wc.site as Site");
		personList.add("p.title as Title");
		personList.add("p.firstname as Firstname");
		personList.add("p.surname as Surname");
		personList.add("ed.employeenumber as EmployeeNo");
		personList.add("p.landline as Phone");
		personList.add("p.mobile as Mobile");
		personList.add("p.email as Email");
		personList.add("p.dateofbirth as DateOfBirth");
		personList.add("p.gender as Gender");
		personList.add("p.unionmemberno as UnionMemberNo");
		personList.add("p.countryoforigin as CountryOfOrigin");
		personList.add("p.dateofmigration as DateOfMigration");
		personList.add("p.firstlanguage as FirstLanguage");
		personList.add("p.formaledhighlevel as HighestLevel");
		personList.add("p.formaledcountry as CountryObtained");
		personList.add("ed.datestarted as StartDate");
		personList.add("ed.dateleaving as DateFinished");
		personList.add("ed.position as Position");
		personList.add("ed.status as Status");
		personList.add("ed.status as ShiftTime");
		personList.add("ed.sector as TCFSector");
		return personList;
	}

	private List<String> getAddressList() {
		List<String> personList = new ArrayList<String>();
		personList.add("a.street as Street");
		personList.add("a.suburb as Suburb");
		personList.add("a.state as State");
		personList.add("a.postcode as Postcode");
		return personList;
	}

	private List<String> getCourseList() {
		List<String> personList = new ArrayList<String>();
		personList.add("c.name as Name");
		personList.add("c.coursetype as CourseType");
		personList.add("r.name as RTO");
		personList.add("r.campus as RTOCampus");
		personList.add("pc.trainingdate as StartDate");
		personList.add("pc.finishdate as EndDate");
		personList.add("e.amount as CostOfCourse");
		personList.add("fs.sourceName as FundingSource");
		return personList;
	}

	private List<String> getExpenseList() {
		List<String> personList = new ArrayList<String>();
		personList.add("e.description as Description");
		personList.add("e.amount as Total");
		personList.add("fs.sourcename as FundingSource");
		return personList;
	}

	protected String getDirectory() {
		return SYSTEM_DIR + "/" + DIRECTORY_NAME;
	}
}
